Design and Use of the Microsoft Excel Solver

We describe the design and use of the spreadsheet optimizer that is bundled with Microsoft Excel. We explain why we and Microsoft made certain choices in designing its user interface, model processing, and solution algorithms for linear, nonlinear and integer programs. We describe some of the common pitfalls encountered by users, and remedies available in the latest version of Microsoft Excel. We briefly survey applications of the Solver and its impact in industry and education. Since its introduction in February 1991, the Microsoft Excel Solver has become the most widely distributed and almost surely the most widely used general-purpose optimization modeling system. Bundled with every copy of Microsoft Excel and Microsoft Office shipped during the last eight years, the Excel Solver is in the hands of 80 to 90 percent of the 35 million users of office productivity software worldwide. The remaining 10 to 20 percent of this audience use either Lotus 1-2-3 or Quattro Pro, both of which now include very similar spreadsheet solvers, based on the same technology used in the Excel Solver. This widespread availability has spawned many applications in industry and government. In education, increasing numbers of MBA and undergraduate business instructors have adopted the Excel Solver as their tool for introducing students to optimization; most management science textbooks now include coverage of the Excel Solver, and several recent texts use it exclusively in the optimization chapters. We review the background and design philosophy of the Excel Solver. We seek to explain why the Excel Solver works the way it does, to clear up some common misunderstandings and pitfalls, and to suggest ideas for good modeling practice when using spreadsheet optimization – described under the Modeling Practice headings. We also briefly survey applications of the Excel Solver in industry and education and describe how practitioners who are not affiliated with the OR/MS community use it. The example models in this paper are available on Practice Online at (http://silmaril.smeal.psu.edu/pol.html) and at http://www.frontsys.com/interfaces.htm. Much more information – over 200 web pages at this writing – is available on Frontline Systems’ World Wide Web site (http://www.frontsys.com). The Microsoft Excel Solver combines the functions of a graphical user interface (GUI), an algebraic modeling language like GAMS [Brooke, Kendrick, and Meeraus 1992] or AMPL [Fourer, Gay, and Kernighan 1993], and optimizers for linear, nonlinear, and integer programs. Each of these functions is integrated into the host spreadsheet program as closely as possible. Many of the decisions we and Microsoft made in designing the Solver were motivated by this goal of seamless integration. Optimization in Microsoft Excel begins with an ordinary spreadsheet model. The spreadsheet’s formula language functions as the algebraic language used to define the model. Through the Solver’s GUI, the user specifies an objective and constraints by pointing and clicking with a mouse, and filling in dialog boxes. The Solver then analyzes the complete optimization model and produces the matrix form required by the optimizers in much the same way that GAMS and AMPL do. The optimizers employ the simplex, generalized reduced gradient, and branch and bound methods to find an optimal solution and sensitivity information. The Solver uses the solution values to update the model spreadsheet, and provides sensitivity and other summary information on additional report spreadsheets. Background and Design Philosophy of the Excel Solver The Microsoft Excel Solver and its counterparts in Lotus 1-2-3 97 and Corel Quattro Pro were not the first spreadsheet optimizers; that distinction belongs to What’sBest!, conceived by Sam Savage, Linus Schrage, and Kevin Cunningham in 1985 and marketed by General Optimization Inc. for the Lotus 1-2-3 Release 2 spreadsheet [Savage 1985]. What’sBest! is still available in versions for each of the major spreadsheets and is now sold and supported by Lindo Systems Inc. Other early spreadsheet optimizers included Frontline Systems’ What-If Solver [Frontline Systems 1990], Enfin Software’s Optimal Solutions [Enfin Software 1988], and Lotus Development’s Solver in earlier versions of 1-2-3 [Lotus Development 1990]. The design approach of What-If Solver, implemented in the graphical user interface of Excel, was chosen by Microsoft over several alternatives including What’sBest!; by Borland (the original developers of Quattro Pro) over an earlier solver developed internally by that company; and later by Lotus over their own internally developed solver. A major reason for this outcome, we believe, is that the Excel Solver had as its design goal "making optimization a feature of spreadsheets," whereas other packages, such as What’sBest!, "use the spreadsheet to do optimization." In many small ways, the Excel Solver caters to the tens of millions of spreadsheet users, rather than to the tens of thousands of OR/MS professionals. Although OR/MS professionals readily learn to use the Excel Solver, they often find certain aspects of its design puzzling or at least different from their expectations. In most cases the differences are due to (1) the architecture of spreadsheet programs, (2) the expectations of the majority of spreadsheet users who are not OR/MS professionals, or (3) the desires of the spreadsheet vendors (Microsoft in the case of the Excel Solver). The Architecture of Spreadsheet Programs Because of the architecture of spreadsheet programs, it is easy to create spreadsheet models that contain discontinuous functions or even nonnumeric values. These models usually cannot be solved with classical optimization methods. The spreadsheet’s formula language is designed for general computations and not just for optimization. Indeed, Excel supports a rich variety of operators and several hundred built-in functions, as well as user-written functions. In contrast, GAMS, AMPL and similar modeling languages include only a small set of operators and functions sufficient for expressing linear, smooth nonlinear, and integer optimization models. The Expectations of Spreadsheet Users The Excel Solver was designed to meet the expectations of spreadsheet users – in particular, users of earlier versions of Excel – rather than traditional OR/MS professionals. An example is the terminology it uses in dialog boxes, such as "Target Cell" (for the objective) and "Changing Cells" (for the decision variables). We used these terms – at Microsoft’s request – to mirror the terms used in the Goal Seek feature, which predated the Solver in Excel and in other spreadsheet programs. The Goal Seek feature, which spreadsheet users often describe as "what-if in reverse," solves a nonlinear function of one variable for a specified value. Spreadsheet users see the Excel Solver as a more powerful successor to the Goal Seek feature [Person et al. 1997]. Figure 1 shows Excel’s Goal Seek dialog box, and Figure 2 shows the Solver Parameters dialog box with its similar terminology. Figure 1: The Goal Seek feature of Microsoft Excel predated the Solver. This feature uses iterative methods to solve a simple equation (formula in the "set cell" equal to the "value") in one variable (the "changing cell"). Figure 2: The Solver Parameters dialog is used to define the optimization model. The terms "set target cell" (for the objective) and "changing cells" (for the variables), and the "value of" option were derived from the earlier Goal Seek feature The Desires of the Spreadsheet Vendors The influence of the spreadsheet vendors’ desires is reflected in the way the Solver determines whether the model is linear or nonlinear. By default, the Solver assumes that the model is nonlinear. The user must select the Assume Linear Model check box in the Solver Options dialog box to override this assumption; the Solver does not attempt to automatically determine whether the model is linear by inspecting the formulas making up the model. Most of Excel’s several hundred built-in functions and all user-written functions would have to be treated as "not linear" (smooth nonlinear or discontinuous, over their full domains) in an automatic test. But users sometimes create models using these functions and then add constraints that result in a linear model over the feasible region. Microsoft wanted a general approach that would support such cases and specified the use of the check box, as well as the use of the nonlinear solver as the default choice. The Role of Bundled Spreadsheet Solvers The "free" bundled version of the Excel Solver described in this paper and similar products, such as What’sBest! Personal Edition, represent the low end of the range of spreadsheet solver functionality, capacity, and performance. More powerful versions are available and these versions are most often used to solve problems in industry. For example, where the standard Excel Solver supports just 200 decision variables, Frontline Systems’ Large-Scale LP Solver (a component of the Premium Solver Platform) supports up to 16,000 variables, and Lindo Systems’ What’sBest! Extended Edition supports up to 32,000 variables. Table 1 summarizes the characteristics of the Premium Solver products offered by Frontline Systems. Table 1. The characteristics of the enhanced Excel Solvers are summarized in this table. For integer problems, "B&B" refers to Branch and Bound, "P&P" refers to Preprocessing and Probing. For nonlinear problems, "GRG" refers to the Generalized Reduced Gradient method and "SQP" refers to Sequential Quadratic Programming.